﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace WebServiceSmartphone
{
    public class DAL
    {
        public DAL()
        {
            //strconn = strconn + ";username=thanhtrungpc";
            //cnn = new SqlConnection(strConnect);
            //cmd = cnn.CreateCommand(); 
        }
        private string strconn = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString+" uid=sa; pwd=moonlight";
        public bool IsExistFromSQL(string sql, CommandType ct, ref string error, params SqlParameter[] param)
        {

            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(sql, Conn) { CommandType = ct })
                {
                    Array.ForEach(param, p => Comm.Parameters.Add(p));

                    Object obj = null;

                    try
                    {
                        Conn.Open();
                        obj = Comm.ExecuteScalar();
                    }

                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }

                    finally
                    {
                        Conn.Close();
                    }

                    return obj == null ? false : true;
                }
            }
        }

        public bool IsExistFromSQL(string sql, CommandType ct, ref string error)
        {

            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(sql, Conn) { CommandType = ct })
                {

                    Object obj = null;

                    try
                    {
                        Conn.Open();
                        obj = Comm.ExecuteScalar();
                    }

                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }

                    finally
                    {
                        Conn.Close();
                    }

                    return obj == null ? false : true;
                }
            }
        }

        public DataSet MyExecuteQuery(ref string error, string strSQL, CommandType ct, params SqlParameter[] param)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
                {
                    Array.ForEach(param, p => Comm.Parameters.Add(p));
                    using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            adp.Fill(ds);
                        }
                        catch (SqlException ex)
                        {
                            error = ex.Message;
                        }
                        return ds;
                    }
                }
            }
        }
        public DataSet MyExecuteReader(ref string error, string strSQL, CommandType ct)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
                {
                    using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            adp.Fill(ds);
                        }
                        catch (SqlException ex)
                        {
                            error = ex.Message;
                        }
                        return ds;
                    }
                }
            }
        }
        public DataSet MyExecuteReader(ref string error, string strSQL, CommandType ct, string database, string servername)
        {
            string connstr = "Server=" + servername + "; Database=" + database + "; Connect Timeout=150; uid=admin; pwd=baccamay ";
            using (SqlConnection Conn = new SqlConnection(connstr))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
                {
                    using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            adp.Fill(ds);
                        }
                        catch (SqlException ex)
                        {
                            error = ex.Message;
                        }
                        return ds;
                    }
                }
            }
        }
        public bool MyExecuteNonQuery(string strSQL, CommandType ct, ref string error, params SqlParameter[] param)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                {
                    bool f = false;
                    Comm.Parameters.Clear();
                    Comm.CommandText = strSQL;
                    Comm.CommandType = ct;
                    Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                    try
                    {
                        Conn.Open();
                        Comm.ExecuteNonQuery();
                        f = true;
                    }
                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }
        public bool MyExecuteNonQueryWithTransaction(ref string error, string strSQL1, string strSQL2, CommandType ct, string[] paraNameOfstrSQL2, DataTable tableValueOfstrSQL2, string[] ColumNameNeedValue2, params SqlParameter[] param1)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                Conn.Open();
                using (SqlTransaction tran = Conn.BeginTransaction(IsolationLevel.Serializable))
                {
                    bool f = false;
                    try
                    {
                        using (SqlCommand Comm1 = new SqlCommand(strSQL1, Conn))
                        {
                            Comm1.Transaction = tran;
                            Comm1.Parameters.Clear();
                            Comm1.CommandText = strSQL1;
                            Comm1.CommandType = ct;
                            Array.ForEach(param1, paramItem => Comm1.Parameters.Add(paramItem));
                            Comm1.ExecuteNonQuery();
                        }
                        using (SqlCommand Comm2 = new SqlCommand(strSQL2, Conn))
                        {
                            Comm2.Transaction = tran;
                            Comm2.CommandText = strSQL2;
                            Comm2.CommandType = ct;
                            int count = 0;
                            foreach (DataRow dr in tableValueOfstrSQL2.Rows)
                            {
                                Comm2.Parameters.Clear();
                                for (int i = 0; i < paraNameOfstrSQL2.Length; i++)
                                {
                                    Comm2.Parameters.Add(new SqlParameter(paraNameOfstrSQL2[i], dr[ColumNameNeedValue2[i]]));
                                }
                                Comm2.ExecuteNonQuery();
                                count++;
                            }
                            if (count == tableValueOfstrSQL2.Rows.Count)
                            {
                                tran.Commit();
                                f = true;
                            }
                            else
                            {
                                tran.Rollback();
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        tran.Rollback();
                        error = ex.Message;
                        return false;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }

        public bool MyExecuteNonQueryWithTransaction(ref string error, string strSQL, CommandType ct, string[] paraNameOfstrSQL, DataTable tableValueOfstrSQL, string[] ColumNameNeedValue)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                Conn.Open();
                using (SqlTransaction tran = Conn.BeginTransaction(IsolationLevel.Serializable))
                {
                    bool f = false;
                    try
                    {

                        using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                        {
                            Comm.Transaction = tran;
                            Comm.CommandText = strSQL;
                            Comm.CommandType = ct;
                            int count = 0;
                            foreach (DataRow dr in tableValueOfstrSQL.Rows)
                            {
                                Comm.Parameters.Clear();
                                for (int i = 0; i < paraNameOfstrSQL.Length; i++)
                                {
                                    Comm.Parameters.Add(new SqlParameter(paraNameOfstrSQL[i], dr[ColumNameNeedValue[i]]));
                                }
                                Comm.ExecuteNonQuery();
                                count++;
                            }
                            if (count == tableValueOfstrSQL.Rows.Count)
                            {
                                tran.Commit();
                                f = true;
                            }
                            else
                            {
                                tran.Rollback();
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        tran.Rollback();
                        error = ex.Message;
                        //return false;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }
        public bool MyExecuteNonQuery(string strSQL, CommandType ct, ref string error, ref string output, string param_output, SqlDbType type_output, params SqlParameter[] param)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                {
                    bool f = false;
                    Comm.Parameters.Clear();
                    Comm.CommandText = strSQL;
                    Comm.CommandType = ct;
                    Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                    Comm.Parameters.Add(param_output, type_output);
                    Comm.Parameters[param_output].Direction = ParameterDirection.Output;

                    //Comm.Parameters.Get
                    try
                    {
                        Conn.Open();
                        Comm.ExecuteNonQuery();
                        output = Comm.Parameters[param_output].Value.ToString();
                        f = true;
                    }
                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }


        public bool MyExecuteNonQuery(string strSQL, CommandType ct, ref string error)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                {
                    bool f = false;
                    Comm.Parameters.Clear();
                    Comm.CommandText = strSQL;
                    Comm.CommandType = ct;
                    //Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                    try
                    {
                        Conn.Open();
                        Comm.ExecuteNonQuery();
                        f = true;
                    }
                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }

        public DataTable GetTable(string sqlCmdText, CommandType cmdType)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(sqlCmdText, Conn))
                {
                    DataTable dt = new DataTable();
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        //Comm.CommandText = sqlCmdText;
                        Comm.CommandType = cmdType;
                        try
                        {
                            Conn.Open();
                            da.SelectCommand = Comm;
                            da.Fill(dt);
                        }
                        catch (SqlException ex)
                        {
                            //return dt;
                        }
                        finally
                        {
                            Conn.Close();
                        }
                    }
                    return dt;
                }
            }
        }
        public DataTable GetTable(string sqlCmdText)
        {
            CommandType cmdType = CommandType.Text;
            
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(sqlCmdText, Conn))
                {
                    DataTable dt = new DataTable();
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        //Comm.CommandText = sqlCmdText;
                        Comm.CommandType = cmdType;
                        try
                        {
                            Conn.Open();
                            da.SelectCommand = Comm;
                            da.Fill(dt);
                        }
                        catch (SqlException ex)
                        {
                            //return dt;
                        }
                        finally
                        {
                            Conn.Close();
                        }
                    }
                    return dt;
                }
            }
        }
        public DataSet GetDataFromSQL(String sql, String table)
        {

            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, Conn))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, table);
                    }
                    catch
                    {
                        return ds;
                    }
                    return ds;
                }
            }
        }
        //Khoi Viet
        public DataTable get_data_table(string procedure)
        {
            SqlConnection con = new SqlConnection(strconn);
            SqlDataAdapter da = new SqlDataAdapter(procedure, con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }

        //A.Phu
        public string MyExecuteQuerys(ref string error, string strSQL, CommandType ct, params SqlParameter[] param)
        {
            string values = "";
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
                {
                    Array.ForEach(param, p => Comm.Parameters.Add(p));
                    using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                    {
                        DataSet ds = new DataSet();
                        try
                        {

                            try
                            {
                                adp.Fill(ds);
                                if (ds.Tables[0].Rows.Count > 0)
                                {
                                    values = ds.Tables[0].Rows[0][0].ToString();
                                }
                            }
                            catch (SqlException ex)
                            {
                                error = ex.Message;
                            }
                        }
                        catch { return values; }
                        return values;
                    }
                }
            }
        }
        public string MyExecuteReaders(ref string error, string strSQL, CommandType ct)
        {
            string tmp = "";
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
                {
                    using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            adp.Fill(ds);
                            if (ds.Tables.Count > 0)
                            {
                                if (ds.Tables[0].Rows.Count > 0)
                                {
                                    tmp = ds.Tables[0].Rows[0][0].ToString();
                                }
                            }
                        }
                        catch (SqlException ex)
                        {
                            error = ex.Message;
                        }
                        return tmp;
                    }
                }
            }
        }

        public bool MyExecuteNonQuerys(string strSQL, CommandType ct, ref string error)
        {
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                {
                    bool f = false;
                    Comm.Parameters.Clear();
                    Comm.CommandText = strSQL;
                    Comm.CommandType = ct;
                    //Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                    try
                    {
                        Conn.Open();
                        Comm.ExecuteNonQuery();
                        f = true;
                    }
                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }
        public bool MyExecuteNonQuerys(string strSQL)
        {
            string error = "";
            CommandType ct = CommandType.Text;
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                {
                    bool f = false;
                    Comm.Parameters.Clear();
                    Comm.CommandText = strSQL;
                    Comm.CommandType = ct;
                    //Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                    try
                    {
                        Conn.Open();
                        Comm.ExecuteNonQuery();
                        f = true;
                    }
                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }
        public string GetString(string strSQL)
        {
            CommandType ct = CommandType.Text;
            string tmp = "",error="";
            using (SqlConnection Conn = new SqlConnection(strconn))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
                {
                    using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            adp.Fill(ds);
                            if (ds.Tables.Count > 0)
                            {
                                if (ds.Tables[0].Rows.Count > 0)
                                {
                                    tmp = ds.Tables[0].Rows[0][0].ToString();
                                }
                            }
                        }
                        catch (SqlException ex)
                        {
                            error = ex.Message;
                        }
                        return tmp;
                    }
                }
            }
        }
        public bool MyExecuteNonQuerys(string strSQL, string servername, string database)
        {

            string connstr = "Server=" + servername + "; Database=" + database + "; Connect Timeout=150; uid=admin; pwd=baccamay ";
            using (SqlConnection Conn = new SqlConnection(connstr))
            {
                using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
                {
                    bool f = false;
                    Comm.Parameters.Clear();
                    Comm.CommandText = strSQL;
                    Comm.CommandType = CommandType.Text;
                    //Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                    try
                    {
                        Conn.Open();
                        Comm.ExecuteNonQuery();
                        f = true;
                    }
                    catch (SqlException ex)
                    {
                        //error = ex.Message;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return f;
                }
            }
        }
    }
}
